IF OBJECT_ID('dbo.RptCSCRExecutedAgreementsSummary') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRExecutedAgreementsSummary
    IF OBJECT_ID('dbo.RptCSCRExecutedAgreementsSummary') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRExecutedAgreementsSummary >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRExecutedAgreementsSummary >>>'
END
go     
-- =======================================================================      
-- Procedure Name: dbo.RptCSCRExecutedAgreementsSummary      
--      
-- Author:      Surajit Paul      
--      
-- Create date: 08/13/2009      
--      
-- Description: Source of Report, "CSCRExecutedAgreementsSummary"      
--                  In Insight DB Reporting Services      
--      
-- Lists all corporate-sponsored clinical research agreements executed       
-- within a specified time period (i.e., 3 months, YTD, annual); distributed       
-- quarterly to MGH/BWH/PHS VPs and department chiefs and ad hoc upon request       
-- by senior leadership.      
--      
-- Parameters:        
--             @StartDate          Date of Status "CSR Executed"      
--             @EndDate                  
--             @CSCRCaseManager               
--             @Institution           
--             @Department      
--             @Unit           
--      @IncludeDanaFarber       
--      
-- Returns:     INT, 0=Success, -1=Error      
--      
-- SampleCall:      
-- EXECUTE dbo.RptCSCRExecutedAgreementsSummary()      
--      
--------------------------------------------------------------------------      
-- Date        Initials  Modification      
--------------------------------------------------------------------------      
-- 08/13/09     SP        Created      
--      
-- =======================================================================      
    
    
    
CREATE PROCEDURE [dbo].[RptCSCRExecutedAgreementsSummary]      
     (      
     @StartDate                         DATETIME,        
     @EndDate                           DATETIME,      
     @CSCRCaseManager                   VARCHAR(100) = NULL,      
     @Institution                       VARCHAR(100) = NULL,                   
     @Department                        VARCHAR(100) = NULL,      
     @Unit                              VARCHAR(100) = NULL,      
     @IncludeDanaFarber                 VARCHAR(3) = NULL,      
     @DomainUserId                      INT      
     )       
                                         
AS      
BEGIN      
     -- Initial Settings      
     SET NOCOUNT ON      
           
     -- Declare Local Variables      
     DECLARE   @Error INT,      
               @RowCount INT,      
               @ConversionDate1 DATETIME,      
               @ConversionDate2 DATETIME,      
      @ReturnCode         INT      
      
     -- Default this, Records with these PropStatDate(s) will not be returned      
     SET  @ConversionDate1 = '8/3/2007'      
     SET  @ConversionDate2 = '8/4/2007'      
                     
     -- ALL checking      
     IF @CSCRCaseManager IN ('ALL','','0') SET @CSCRCaseManager = NULL      
     IF @Institution IN ('ALL','','0') SET @Institution = NULL      
     IF @Department IN ('ALL','','0') SET @Department = 'ALL'      
     IF @Unit IN ('ALL','','0') SET @Unit = 'ALL'
     IF @IncludeDanaFarber IN ('Yes','1') SET @IncludeDanaFarber = NULL ELSE SET @IncludeDanaFarber = '2'      
      
      
 DECLARE   @Agreements TABLE (      
                    PrincipalInvestigator    VARCHAR(100)    NULL,      
     ProposalTitle    VARCHAR(800)    NULL,      
     PrimarySponsor    VARCHAR(200)    NULL,      
     SubActivityType    VARCHAR(100)    NULL,      
     ApprovalDate             DATETIME       NULL,      
     SecondarySponsor         VARCHAR(100)    NULL,      
     SubAgreementType   VARCHAR(100)    NULL,      
     InPatientCareTotal   MONEY          NULL,      
     OneTimeFees     MONEY          NULL,      
     ExpectedPatientEnrollment MONEY          NULL,      
     FARate      MONEY          NULL,      
     ProjectDirectCost   MONEY          NULL,       
     PropBudStat     VARCHAR(100)    NULL,      
     PropBudStatDate    DATETIME       NULL,      
     PropStat     VARCHAR(300)    NULL,      
     PropStatDate    DATETIME       NULL,      
     FullyExecutedAwardedDate DATETIME       NULL,      
     CSCRCaseManager    VARCHAR(100)    NULL,      
     CSCRCaseManagerName   VARCHAR(100)    NULL,      
     AgreementType    VARCHAR(100)    NULL,      
     TransactionType    VARCHAR(100)    NULL,      
     ProposalType    VARCHAR(100)    NULL,      
     Institution     VARCHAR(100)    NULL,      
     Department     VARCHAR(100)    NULL,      
     ChiefCode                VARCHAR(4)     NULL,      
     UnitCode                 VARCHAR(100)    NULL,      
     ProposalStatus    VARCHAR(100)    NULL,      
     InfoEdPropNumber         VARCHAR(100)    NULL,      
     PrincipalInvestigatorId  INT            NULL,      
     SponsorId     VARCHAR(100)    NULL,      
     AgrResourceId    VARCHAR(100)    NULL,      
     FolderNumber             VARCHAR(20)    NULL,      
     AmendmentFolderNumber    VARCHAR(20)    NULL,      
                    AgreementId              INT            NULL,      
     AmendFirstPropStatChgDate DATETIME       NULL,      
     MstrFirstPropStatChgDate DATETIME       NULL,      
     LegFEAPDate     DATETIME       NULL,      
     SubContractName    VARCHAR(100)    NULL,      
     SubContractTranType   VARCHAR(100)    NULL,      
     SubContractStatus   VARCHAR(100)    NULL,      
     SubContractStatusDate  DATETIME       NULL,      
     InstitutionId            INT            NULL,      
                    DepartmentId             INT            NULL,      
                    UnitId                   INT            NULL)      
                
     -- Let Detail Proc do the work.      
     INSERT    @Agreements      
     (PrincipalInvestigator,      
     ProposalTitle,      
     PrimarySponsor,      
     SubActivityType,      
     ApprovalDate,      
     SecondarySponsor,      
     SubAgreementType,      
     InPatientCareTotal,      
     OneTimeFees,      
     ExpectedPatientEnrollment,      
     FARate,      
     ProjectDirectCost,       
     PropBudStat,      
     PropBudStatDate,      
     PropStat,      
     PropStatDate,      
     FullyExecutedAwardedDate,      
     CSCRCaseManager,      
     CSCRCaseManagerName,      
     AgreementType,      
     TransactionType,      
     ProposalType,      
     Institution,      
     Department,      
     ChiefCode,      
     UnitCode,      
     ProposalStatus,      
     InfoEdPropNumber,      
     PrincipalInvestigatorId,      
     SponsorId,      
     AgrResourceId,      
     FolderNumber,      
     AmendmentFolderNumber,      
                    AgreementId,      
     AmendFirstPropStatChgDate,      
     MstrFirstPropStatChgDate,      
     LegFEAPDate,      
     SubContractName,      
     SubContractTranType,      
     SubContractStatus,      
     SubContractStatusDate,      
        InstitutionId,      
        DepartmentId,      
                    UnitId)      
      
 EXECUTE @ReturnCode = dbo.RptCSCRExecutedAgreementsDetails      
                              @StartDate               = @StartDate,      
                              @EndDate                 = @EndDate,      
         @CSCRCaseManager     = @CSCRCaseManager,       
         @Institution             = @Institution,      
         @Department              = @Department,      
         @Unit                    = @Unit,      
         --@AgreeType      = 'ALL',       
         @IncludeDanaFarber    = @IncludeDanaFarber,      
         @DomainUserId            = @DomainUserId      
        
                                    
  -- check for errors      
     SELECT @Error=@@ERROR      
     IF @Error != 0 OR @ReturnCode != 0      
     BEGIN      
          RETURN -1      
     END      
      
      
           
     -- Return Result Set      
     SELECT    A.Institution AS Institution,      
      A.Department AS Department,      
      A.InstitutionId AS InstitutionId,      
               A.DepartmentId AS DepartmentId,      
      A.UnitId AS UnitId,       
               @Unit AS Unit,           
     CASE WHEN @CSCRCaseManager IS NOT NULL  then CSCRCaseManagerName
          ELSE 'ALL' 
     END AS CSCRCaseManager,   
       --@CSCRCaseManager AS CSCRCaseManager,
     --CSCRCaseManagerName  AS CSCRCaseManager,       
               A.ChiefCode AS ChiefCode,      
      A.UnitCode AS UnitCode,      
      COUNT(CASE WHEN SubActivityType IN ('CDA: Confidentiality Agreement')       
                    THEN SubActivityType ELSE NULL      
     END) AS CDA,      
      COUNT(CASE WHEN SubActivityType IN ('Trial - Company Initiated','Trial - Jointly Initiated','Trial - Other','Trial - PI Initiated')       
                    THEN SubActivityType ELSE NULL      
     END) AS CTA,        
      COUNT(CASE WHEN TransactionType  IN ('CSCR-Amendment')       
           THEN TransactionType ELSE NULL      
     END) AS Amendments,       
      COUNT(CASE WHEN SubContractTranType  IN ('CSCR - Subcontract')       
                    THEN SubContractTranType ELSE NULL      
     END) AS Subcontracts,       
      COUNT(CASE WHEN SubActivityType NOT IN ('CDA: Confidentiality Agreement','Trial - Company Initiated','Trial - Jointly Initiated','Trial - Other','Trial - PI Initiated')       
    THEN SubActivityType ELSE NULL      
     END) AS Other      
                         
                     
     FROM @Agreements AS A        
  --WHERE A.ChiefCode != NULL      
  GROUP BY Institution, InstitutionId, Department, DepartmentId, ChiefCode, UnitCode, UnitId,--CSCRCaseManagerName        
           CASE      
                              WHEN @CSCRCaseManager IS NOT NULL  then CSCRCaseManagerName --isnull(@CSCRCaseManager,'ALL')         
                              ELSE 'ALL'    
            END              
       
      
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT      
     IF @Error!=0      
          RETURN -1      
                
     -- Return Success      
     RETURN 0      
      
           
END      
      
     

go

IF OBJECT_ID('dbo.RptCSCRExecutedAgreementsSummary') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRExecutedAgreementsSummary >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRExecutedAgreementsSummary >>>'
go

--GRANT EXECUTE ON dbo.RptCSCRExecutedAgreementsSummary TO InsightReport,Insight
go

